/**
 * 项目名：phms3
 * 日期：    2019-11-22 22:56:55
 * Copyright (c) 2015- HTSC-版权所有
 */

package com.joysuch.wwyt.mh.repository;

import com.joysuch.wwyt.mh.entity.MhMajorHazardChemProduct;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import java.util.List;

/**
 * MhMajorHazardChemProductDao
 */
public interface MhMajorHazardChemProductDao
        extends JpaRepository<MhMajorHazardChemProduct, Long>, QuerydslPredicateExecutor<MhMajorHazardChemProduct> {

    public Page<MhMajorHazardChemProduct> findAll(Specification<MhMajorHazardChemProduct> spec, Pageable pageable);

    public MhMajorHazardChemProduct save(MhMajorHazardChemProduct bean);

    public void delete(MhMajorHazardChemProduct bean);

    public MhMajorHazardChemProduct findByCode(String code);

    @Query(value = "select * from wwyt_mh_major_hazard_chem_product  where DELETE_FLAG ='0' and name =?1 AND (state IS NULL OR state !=44)", nativeQuery = true)
    public MhMajorHazardChemProduct findByName(String name);

    List<MhMajorHazardChemProduct> deleteByName(String name);

    @Query(value = "select count(*) from wwyt_mh_major_hazard_chem_product  where DELETE_FLAG ='0' and name =?1 AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByName(String name);

    @Query(value = "select count(*) from wwyt_mh_major_hazard_chem_product  where DELETE_FLAG ='0' and code =?1 AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByCode(String code);

//    @Query(value ="SELECT ID FROM`wwyt_mh_major_hazard_chem_product` WHERE CAS_NO =?1",nativeQuery = true)
//    Long findIdBYCasNO(String casNo);

    // @Query("select m.id from MhMajorHazardChemProduct m where m.casNo =?1")
    @Query(value = "select ID from wwyt_mh_major_hazard_chem_product  where CAS_NO =?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    Long findIdByCasNO(String casNo);

    @Query("select id from  MhMajorHazardChemProduct where name =?1")
    Long findIdByName(String hName);


    @Query(value = "select * from wwyt_mh_major_hazard_chem_product  where CAS_NO = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44) limit 0,1", nativeQuery = true)
    public MhMajorHazardChemProduct findByCasNo(String casNo);

    @Query(value = "select * from wwyt_mh_major_hazard_chem_product  where DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<MhMajorHazardChemProduct> findAll();

    @Query(value = "select * from wwyt_mh_major_hazard_chem_product  where CODE = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<MhMajorHazardChemProduct> getByCode(String code);

    @Query(value = "select * from wwyt_mh_major_hazard_chem_product  where CAS_NO = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<MhMajorHazardChemProduct> getByCasNo(String casNo);

    @Query(value = "select count(*) from wwyt_mh_major_hazard_chem_product  where CAS_NO = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByCasNo(String casNo);

    @Query(value = "select count(*) from wwyt_mh_major_hazard_chem_product  where CAS_NO = ?1 AND code!=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByCasNoAndCodeNotEq(String casNo, String code);

    @Query(value = "select count(*) from wwyt_mh_major_hazard_chem_product  where name = ?1 AND code!=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    int countByNameAndCodeNotEq(String name, String code);

    @Query(value = "select * from wwyt_mh_major_hazard_chem_product  where CAS_NO = ?1 AND code!=?2 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    List<MhMajorHazardChemProduct> getByCasNoAndCodeNotEq(String casNo, String code);

    @Query(value = " select name from wwyt_mh_major_hazard_chem_product where id = ?1 AND DELETE_FLAG ='0' AND (state IS NULL OR state !=44)", nativeQuery = true)
    String findNameById(Long id);
}
